log using PS_panel_data_v3.log, text replace

*Merge the subsidiary data and the country data.
use C:\Users\Makoto\Dropbox\Orbis_Profit_Shifting_v2\JPUS_subs_parents_2004-2016_v3, clear

encode bvdid, generate(subid)
reshape long fixed_assets intan_assets tangible_assets otherfixed_assets total_assets /*
*/noncur_liab cur_liab num_employees sales ebit pretax_prof taxation aftertax_prof /*
*/netincome costs_employees closingdate /*
*/guo_fixed_assets guo_intan_assets guo_tangible_assets guo_otherfixed_assets guo_total_assets /*
*/guo_noncur_liab guo_cur_liab guo_num_employees guo_sales guo_ebit guo_pretax_prof guo_taxation guo_aftertax_prof /*
*/guo_netincome guo_costs_employees guo_closingdate, i(subid) j(year)

*Create dummy variables that indicate whether a subsidiary is owned by a Japanese or US parent firm.
tabulate guo_ccode
generate JP=0
replace JP=1 if guo_ccode=="JP"
generate US=0
replace US=1 if guo_ccode=="US"

*Distribution of Foreign Subsidiaries across Host Countries
generate sub=0
replace sub=1 if pretax_prof!=. & tangible_assets!=. & costs_employees!=.
bysort country: egen sub_country=total(sub)
table country JP if sub_country>=1
drop sub sub_country

*Replace the country code for the Faroe Islands by the ISO 3166 country code.
replace ccode="FO" if country=="Denmark (Faroe Islands)"
replace country="Faroe Islands" if country=="Denmark (Faroe Islands)"

*Merge the country data into the subsidiary data.
merge m:1 ccode year using PS_country_v2, keepusing(tax gdp gdpgr gdppc unemp pop exchange_normalized2003)
list country ccode if _merge==1
keep if _merge==3
drop _merge

*Create the variables that indicate the corporate tax rates of Japan and the US.
generate tax_JP=42 if year==2004
replace tax_JP=40.69 if year>=2005 & year<=2011
replace tax_JP=38.01 if year==2012 | year==2013
replace tax_JP=35.64 if year==2014
replace tax_JP=33.86 if year==2015
replace tax_JP=30.86 if year==2016

generate tax_US=34 if year==2004
replace tax_US=40 if year>=2005 & year<=2016

replace tax=tax/100
replace tax_JP=tax_JP/100
replace tax_US=tax_US/100

xtset subid year
xtdes

*Retain only foreign subsidiaries in the sample for which information on pre-tax profit, tangible fixed assets, costs of employees, and the host-country tax rate is available.
drop if pretax_prof==. | tangible_assets==. | costs_employees==. | tax==.

count if subid==.
count if year==.
count if US==.
count if JP==.
drop if subid==. | year==. | US==. | JP==.

save JPUS_subs_panel_v8, replace

log close